﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ModelLayer;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data.SqlTypes;

namespace CinemaDbLib
{
	public class ScheduleDB
	{
		private Connection connection;
		private MovieDB movieDB;
		private RoomDB roomDB;

		public ScheduleDB()
		{
			connection = new Connection();
			movieDB = new MovieDB();
			roomDB = new RoomDB();
		}

		public List<Schedule> getSchedules()
		{
			return where("");
		}

		public Schedule getScheduleById(int id)
		{
			return singleWhere("id = " + id);
		}

		public List<Schedule> getScheduleByMovieId(int id)
		{
			return where("movieId = " + id);
		}

		private Schedule singleWhere(String wClause)
		{
			Schedule schedule = null;

			string query = "SELECT * FROM Schedule WHERE " + wClause;
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			if (reader.Read())
			{
				schedule = new Schedule();
				schedule.id = reader.GetInt32(0);
				schedule.time = reader.GetDateTime(1);
				schedule.movie = movieDB.getMovieById(reader.GetInt32(2));
				schedule.room = roomDB.getRoomById(reader.GetInt32(3));
				schedule.price = reader.GetDecimal(4);
			}

			reader.Close();
			connection.Close();

			return schedule;
		}

		private List<Schedule> where(String wClause)
		{
			List<Schedule> schedules = new List<Schedule>();
            string now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string query = "SELECT * FROM Schedule WHERE startingTime > '" + now + "'" + (wClause.Length > 0 ? " AND " + wClause : "") + " ORDER BY startingTime";
			connection.Open();
			SqlDataReader reader = connection.Read(query);
			while (reader.Read())
			{
				Schedule schedule = new Schedule();
				schedule.id = reader.GetInt32(0);
				schedule.time = reader.GetDateTime(1);
				schedule.movie = movieDB.getMovieById(reader.GetInt32(2));
				schedule.room = roomDB.getRoomById(reader.GetInt32(3));
				schedule.price = reader.GetDecimal(4);
				schedules.Add(schedule);
			}

			reader.Close();
			connection.Close();

			return schedules;
		}
	}
}
